package com.nb.protocol.db;

import com.nb.protocol.handle.AcceptorHandler;
import com.nb.protocol.protocol.MessageHolder;
import org.apache.commons.lang3.RandomUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import java.sql.*;
import java.util.Date;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @function:
 * @author: hjw
 * @date: 2018/1/7
 * @company: Sinoregal
 * @copyright: 2018 www.sinoregal.cn. All rights reserved.
 */
public class PostureDao {
    private static final Logger logger = Logger.getLogger(PostureDao.class);

    private static final String sql = "INSERT INTO sensor_posture (sn,create_date,VALUE,lon,lat,location) VALUES (?,?,?,?,?,?) ";
    private static final String sql2 = "UPDATE car_device_value c SET c.`car_deivce_value` = ?,c.`status` = 0,c.`update_time` = ? " +
            "WHERE c.`car_device_sn` = ? AND c.`car_device_type` = 1  ";
    private static final String Vbatsql = "INSERT INTO sensor_vbat (sn,create_date,VALUE) VALUES (?,?,?) ";

    private static final String SELECT_SQL = "SELECT d.lon,d.lat,d.location FROM  " +
            " car_device_r r,(SELECT * FROM car_drive_record WHERE  id IN ( SELECT MAX(id) FROM car_drive_record GROUP BY car_id)) d, " +
            " car_info c,device_info i " +
            " WHERE r.`car_id` = c.`car_id` " +
            " AND d.car_id = c.`car_id` " +
            " AND r.`device_id` = i.`id` AND i.`sn` = ?";

    private static final String SELECT_CAR_ID_SQL = "SELECT c.`car_id` FROM car_device_value c WHERE c.`car_device_sn` = ? AND c.`car_device_type` = 1";

    private static final String CAR_ALARM_SQL = "INSERT INTO car_alarm_detail (car_id,alarm_level,alarm_type,location,DATA) VALUES(?,?,1,?,?)";



    public static void reportData(MessageHolder messageHolder) throws SQLException {
        //String sql = "INSERT INTO sensor_posture (sn,create_date,VALUE) VALUES (?,?,?) ";
        //String sql2 = "UPDATE car_device_value c SET c.`car_deivce_value` = ?,c.`status` = 0,c.`update_time` = ? " +
        //        "WHERE c.`car_device_sn` = ? AND c.`car_device_type` = 1  ";
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String lon = "", lat = "", location = "";
        try {
            connection = DbCtrl.getConnection();
            connection.setAutoCommit(false);

            pstmt = connection.prepareStatement(SELECT_SQL);
            pstmt.setString(1, messageHolder.getCpuId());
            rs = pstmt.executeQuery();
            if (rs.next()) {
                lon = rs.getString(1);
                lat = rs.getString(2);
                location = rs.getString(3);
            }


            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, messageHolder.getCpuId());
            pstmt.setTimestamp(2, new Timestamp(new Date().getTime()));
            pstmt.setInt(3, messageHolder.getBody()[0]);
            pstmt.setString(4, lon);
            pstmt.setString(5, lat);
            pstmt.setString(6, location);
            pstmt.execute();

            insertAlarm(connection,messageHolder.getBody()[0],messageHolder.getCpuId(),location);

            pstmt = connection.prepareStatement(sql2);
            pstmt.setInt(1, messageHolder.getBody()[0]);
            pstmt.setTimestamp(2, new Timestamp(new Date().getTime()));
            pstmt.setString(3, messageHolder.getCpuId());
            pstmt.executeUpdate();

            //aa(messageHolder.getCpuId(),connection);
            connection.commit();

            //connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            logger.error("写入传感器数据出现错误:" + e.getMessage());
            throw new SQLException(e);
        } finally {
            try {
                connection.setAutoCommit(true);
                DbCtrl.close(connection, pstmt, rs);
            } catch (SQLException e) {
            }
        }
    }

    public static void reportDataAndVbat(MessageHolder messageHolder) throws SQLException {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String lon = "", lat = "", location = "";
        try {
            connection = DbCtrl.getConnection();
            connection.setAutoCommit(false);

            pstmt = connection.prepareStatement(SELECT_SQL);
            pstmt.setString(1, messageHolder.getCpuId());
            rs = pstmt.executeQuery();
            if (rs.next()) {
                lon = rs.getString(1);
                lat = rs.getString(2);
                location = rs.getString(3);
            }


            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, messageHolder.getCpuId());
            pstmt.setTimestamp(2, new Timestamp(new Date().getTime()));
            pstmt.setInt(3, messageHolder.getBody()[1]);
            pstmt.setString(4, lon);
            pstmt.setString(5, lat);
            pstmt.setString(6, location);
            pstmt.execute();

            insertAlarm(connection,messageHolder.getBody()[1],messageHolder.getCpuId(),location);

            pstmt = connection.prepareStatement(sql2);
            pstmt.setInt(1, messageHolder.getBody()[1]);
            pstmt.setTimestamp(2, new Timestamp(new Date().getTime()));
            pstmt.setString(3, messageHolder.getCpuId());
            pstmt.executeUpdate();

            pstmt = connection.prepareStatement(Vbatsql);
            pstmt.setString(1, messageHolder.getCpuId());
            pstmt.setTimestamp(2, new Timestamp(new Date().getTime()));
            pstmt.setInt(3, messageHolder.getBody()[0]);
            pstmt.execute();
            //aa(messageHolder.getCpuId(),connection);
            connection.commit();

            //connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            logger.error("写入传感器数据出现错误:" + e.getMessage());
            throw new SQLException(e);
        } finally {
            try {
                connection.setAutoCommit(true);
                DbCtrl.close(connection, pstmt, rs);
            } catch (SQLException e) {
            }
        }
    }

    private static void insertAlarm(Connection connection,int value, String cpuId,String location) throws SQLException{
        int absValue = Math.abs(value);
        if (absValue < 5)
            return;

        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String carId = "";
        try {
            pstmt = connection.prepareStatement(SELECT_CAR_ID_SQL);
            pstmt.setString(1, cpuId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                carId = rs.getString(1);
            }

            if(StringUtils.isNotEmpty(carId)){
                int alarm_level = 1;
                if(absValue >=15){
                    alarm_level = 3;
                }else if(absValue >=10){
                    alarm_level = 2;
                }
                pstmt = connection.prepareStatement(CAR_ALARM_SQL);
                pstmt.setString(1, carId);
                pstmt.setInt(2, alarm_level);
                pstmt.setString(3,location);
                pstmt.setInt(4, value);
                pstmt.execute();
            }
        }finally {
            DbCtrl.close(null,pstmt,rs);
        }
    }


    private final static AtomicInteger dr = new AtomicInteger(100);
    private final static AtomicInteger tf = new AtomicInteger(100);
    private final static String[] shocks = {"ON", "OFF"};
    private final static String[] anti_thefts = {"开门", "关门"};
    private final static String[] empty_weights = {"空", "重"};

    private static final String sql3 = "INSERT INTO sensor_2(sn,create_date,speed,driven_distance,total_fuel,avg_fuel,empty_weight,shock,luminosity,anti_theft,temperature,humidity) " +
            " VALUES (?,?,?,?,?,?,?,?,?,?,?,?) ";

    private static void aa(String sn, Connection connection) {
        int speed = RandomUtils.nextInt(50, 120);
        int driven_distance = dr.incrementAndGet();
        int total_fuel = tf.incrementAndGet();
        int avg_fuel = RandomUtils.nextInt(5, 8);
        String empty_weight = empty_weights[RandomUtils.nextInt(0, 1)];
        String shock = shocks[RandomUtils.nextInt(0, 1)];
        int luminosity = RandomUtils.nextInt(0, 300000);
        String anti_theft = anti_thefts[RandomUtils.nextInt(0, 1)];
        int temperature = RandomUtils.nextInt(10, 20);

        int humidity = RandomUtils.nextInt(80, 90);
        int set_temperature = 15;
        int set_humidity = 90;
        PreparedStatement pstmt = null;
        try {
            pstmt = connection.prepareStatement(sql3);
            pstmt.setString(1, sn);
            pstmt.setTimestamp(2, new Timestamp(new Date().getTime()));

            pstmt.setInt(3, speed);
            pstmt.setInt(4, driven_distance);
            pstmt.setInt(5, total_fuel);
            pstmt.setInt(6, avg_fuel);
            pstmt.setString(7, empty_weight);
            pstmt.setString(8, shock);
            pstmt.setInt(9, luminosity);
            pstmt.setString(10, anti_theft);
            pstmt.setInt(11, temperature);
            pstmt.setInt(12, humidity);
            pstmt.execute();
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            try {
                DbCtrl.close(null, pstmt);
            } catch (SQLException e1) {
            }
        }
    }

}
